﻿using Inaction.DbSchame;
using Inaction.Rule;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;

namespace Inaction.Database.Schema
{
    public abstract class SchemaProviderBase
    {
        #region 属性
        internal virtual int MaxParaCount { get { return 2000; } }
        public List<DbTypeInfo> DbTypeInfos { get; private set; }
        public abstract string ParaFix { get; }

        internal virtual Func<string, string> NameFormater
        {
            get
            {
                return new Func<string, string>((x) => { return x; });
            }
        }
        public DbProviderFactory Factory
        {
            get { return DbProviderManager.GetFactory(SqlType); }
        }
        public string DbName { get; protected set; }
        public string ConnectionString { get; private set; }
        public SqlType SqlType { get; private set; }
        public List<Table> Tables { get; private set; }
        public List<View> Views { get; private set; }
        protected DataTable TableTable { get; private set; }
        public DataTable ViewTable { get; private set; }
        public DataTable ColumnTable { get; private set; }
        public DataTable ViewColumnTable { get; private set; }
        public DataTable ConstraintsTable { get; private set; }
        protected virtual string SqlDataType { get; private set; }
        protected abstract string SqlTables { get; }
        protected abstract string SqlViews { get; }
        protected abstract string SqlColumns { get; }
        protected abstract string SqlConstraints { get; }
        protected virtual string SqlViewColumns { get; set; }
        #endregion

        #region 构造
        public SchemaProviderBase(string connectionstring, SqlType sqlType)
        {
            ConnectionString = connectionstring;
            SqlType = sqlType;
            View.FormatSql = FormatSql;
            Init();
        }
        protected virtual void Init()
        {
            Tables = new List<Table>();
            Views = new List<View>();

            TableTable = new DataTable("Tables");
            TableTable.Columns.Add("TABLENAME");
            TableTable.Columns.Add("COMMENTS");

            ViewTable = new DataTable("Views");
            ViewTable.Columns.Add("VIEWNAME");
            ViewTable.Columns.Add("SQL");

            ColumnTable = new DataTable("Columns");
            ColumnTable.Columns.Add("TABLENAME");
            ColumnTable.Columns.Add("COLUMNNAME");
            ColumnTable.Columns.Add("DATATYPE");
            ColumnTable.Columns.Add("LENGTH");
            ColumnTable.Columns.Add("PRECISION");
            ColumnTable.Columns.Add("SCALE");
            ColumnTable.Columns.Add("NULLABLE");
            ColumnTable.Columns.Add("ORDINAL");
            ColumnTable.Columns.Add("DEFAULTVALUE");
            ColumnTable.Columns.Add("COMMENTS");

            ViewColumnTable = new DataTable("ViewColumns");
            ViewColumnTable.Columns.Add("VIEWNAME");
            ViewColumnTable.Columns.Add("TABLENAME");
            ViewColumnTable.Columns.Add("COLUMNNAME");

            ConstraintsTable = new DataTable("Constraints");
            ConstraintsTable.Columns.Add("CONSTRAINTNAME");
            ConstraintsTable.Columns.Add("TABLENAME");
            ConstraintsTable.Columns.Add("COLUMNNAME");
            ConstraintsTable.Columns.Add("REFERENCETABLE");
            ConstraintsTable.Columns.Add("CONSTRAINTTYPE");

            DbTypeInfos = LoadDataTypes();

            LoadSchema();

            LoadTables();

            LoadViews();
        }
        #endregion

        #region 方法

        #region abstract
        internal protected abstract string GetPageSql(Type type, int pageSize, int pageIndex);
        public abstract string CreateScript(Table table);
        public abstract string CreateDataInsertSql(Table table);
       
        #endregion
       
        #region virtual

        protected virtual bool IsEnumType(Type type)
        {
            return (type.Equals(typeof(System.SByte))
                || type.Equals(typeof(System.Byte))
                || type.Equals(typeof(System.UInt16))
                || type.Equals(typeof(System.Int16)));
        }
        internal virtual string FormatSql(string sql)
        {
            return sql;
        }       
        protected virtual void LoadSchema()
        {
            #region 表结构数据
            LoadTableSchema();
            #endregion

            #region 视图结构数据
            LoadViewSchema();
            #endregion

            #region 列结构数据
            LoadColumnSchema();
            LoadViewColumnSchame();
            #endregion

            #region 约束
            LoadConstraintsSchema();
            #endregion
        }
        protected virtual void LoadTableSchema()
        {
            using (var connection = GetConnection())
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = SqlTables;
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    var row = TableTable.NewRow();
                    row["TABLENAME"] = reader["TABLENAME"];
                    row["COMMENTS"] = reader["COMMENTS"];
                    TableTable.Rows.Add(row);
                }
            }
        }
        protected virtual void LoadViewSchema()
        {
            using (var connection = GetConnection())
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = SqlViews;
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    var row = ViewTable.NewRow();
                    row["VIEWNAME"] = reader["VIEWNAME"];
                    row["SQL"] = reader["SQL"];
                    ViewTable.Rows.Add(row);
                }
            }
        }
        protected virtual void LoadViewColumnSchame()
        {
            if (string.IsNullOrEmpty(SqlViewColumns)) return;
            using (var connection = GetConnection())
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = SqlViewColumns;
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    var row = ViewColumnTable.NewRow();
                    row["VIEWNAME"] = reader["VIEWNAME"];
                    row["TABLENAME"] = reader["TABLENAME"];
                    row["COLUMNNAME"] = reader["COLUMNNAME"];
                    ViewColumnTable.Rows.Add(row);
                }
            }
        }
        protected virtual void LoadColumnSchema()
        {
            using (var connection = GetConnection())
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = SqlColumns;
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    var row = ColumnTable.NewRow();
                    row["TABLENAME"] = reader["TABLENAME"];
                    row["COLUMNNAME"] = reader["COLUMNNAME"];
                    row["DATATYPE"] = reader["DATATYPE"];
                    row["LENGTH"] = reader["LENGTH"];
                    row["PRECISION"] = reader["PRECISION"];
                    row["SCALE"] = reader["SCALE"];
                    row["NULLABLE"] = reader["NULLABLE"];
                    row["ORDINAL"] = reader["ORDINAL"];
                    row["DEFAULTVALUE"] = reader["DEFAULTVALUE"];
                    row["COMMENTS"] = reader["COMMENTS"];
                    ColumnTable.Rows.Add(row);
                }
            }
        }
        protected virtual void LoadConstraintsSchema()
        {
            /*CONSTRAINTTYPE:约束类型:U:惟一索引,P:主键,R:外键,C:Check约束*/
            using (var connection = GetConnection())
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = SqlConstraints;
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    var row = ConstraintsTable.NewRow();
                    row["CONSTRAINTNAME"] = reader["CONSTRAINTNAME"];
                    row["TABLENAME"] = reader["TABLENAME"];
                    row["COLUMNNAME"] = reader["COLUMNNAME"];
                    row["REFERENCETABLE"] = reader["REFERENCETABLE"];
                    row["CONSTRAINTTYPE"] = reader["CONSTRAINTTYPE"];
                    ConstraintsTable.Rows.Add(row);
                }
            }
        }
        internal virtual void ParseException(Exception ex)
        {
            throw ex;
        }
        internal virtual string ParseBatchSql(string sql)
        {
            return sql;
        }
        protected virtual List<DbTypeInfo> LoadDataTypes()
        {
            #region 数据类型
            DataTable dbTypeSchema = null;
            if (string.IsNullOrEmpty(SqlDataType))
            {
                dbTypeSchema = GetSchema(GetConnection(), "DataTypes");
            }
            else
            {
                using (var connection = GetConnection())
                {
                    connection.Open();
                    var command = connection.CreateCommand();
                    command.CommandText = SqlDataType;
                    var reader = command.ExecuteReader();
                    dbTypeSchema = new DataTable();
                    dbTypeSchema.Columns.Add("TYPENAME");
                    dbTypeSchema.Columns.Add("DATATYPE");
                    while (reader.Read())
                    {
                        var row = dbTypeSchema.NewRow();
                        row["TYPENAME"] = reader["TYPENAME"];
                        row["DATATYPE"] = reader["DATATYPE"];
                    }
                }
            }

            List<DbTypeInfo> dbTypeInfos = new List<DbTypeInfo>();

            foreach (DataRow row in dbTypeSchema.Rows)
            {
                var typeInfo = new DbTypeInfo
                {
                    DbType = row["TypeName"].ToString(),
                    TypeName = row["DataType"].ToString()
                };
                dbTypeInfos.Add(typeInfo);
            }
            #endregion

            return dbTypeInfos;
        }
        public virtual string GetPropertyType(Type systemType, bool nullable)
        {
            var type = "string";
            if (systemType.IsDateTime())
                type = "DateTime";
            if (systemType.IsFloat())
                type = "decimal";
            if (systemType.IsInt())
                type = "int";
            if (systemType.IsLong())
                type = "long";
            if (systemType.IsShort())
                type = "short";
            if (nullable && !systemType.IsString())
            {
                return type + "?";
            }
            return type;
        }
        public virtual DbConnection GetConnection()
        {
            var con = Factory.CreateConnection();
            con.ConnectionString = ConnectionString;
            if (DbName == null)
            {
                DbName = con.Database;
            }
            return con;
        }
        #endregion

        #region static
        private static void InitViewByTable(Table table, View view)
        {
            foreach (var column in table.Columns)
            {
                View.ColumnNameAlias n = view.ColumnNames.FirstOrDefault(p => p.Name == column.Name && p.TableName.Equals(table.Name, StringComparison.OrdinalIgnoreCase));
                if (n == null)
                {
                    n = view.ColumnNames.FirstOrDefault(p => p.Name == column.Name && string.IsNullOrEmpty(p.TableName));
                }
                if (n != null)
                {
                    var viewColumn = view.Columns.FirstOrDefault(p => p.Name == n.Alias || p.Name == n.Name);
                    if (viewColumn == null || viewColumn.Table != view) continue;
                    viewColumn.DbType = column.DbType;
                    viewColumn.DefaultValue = column.DefaultValue;
                    viewColumn.DisplayName = column.DisplayName;
                    viewColumn.IsPrimaryKey = column.IsPrimaryKey;
                    viewColumn.Length = column.Length;
                    viewColumn.Nullable = column.Nullable;
                    viewColumn.Ordinal = column.Ordinal;
                    viewColumn.Precision = column.Precision;
                    viewColumn.Scale = column.Scale;
                    viewColumn.SystemType = column.SystemType;
                    viewColumn.Table = table;
                    viewColumn.IsEnum = column.IsEnum;
                }
            }
            foreach (var foreignKey in table.ForeignKeys)
            {
                var c = view.Columns.FirstOrDefault(p => p.Name == foreignKey.Column.Name);
                if (c == null) continue;
                view.ForeignKeys.Add(
                    new ForeignKey
                    {
                        Column = c,
                        DisplayName = foreignKey.DisplayName,
                        Name = foreignKey.Name,
                        ReferenceTable = foreignKey.ReferenceTable,
                        Table = foreignKey.Table
                    });
                c.IsForeignKey = true;
            }
            foreach (var uniqueKey in table.UniqueKeys)
            {
                var uk = new UniqueKey()
                {
                    DisplayName = uniqueKey.DisplayName,
                    Name = uniqueKey.Name,
                    Table = uniqueKey.Table
                };
                foreach (var column in uniqueKey.Columns)
                {
                    var c = view.Columns.FirstOrDefault(p => p.Name == column.Name);
                    if (c == null) continue;
                    uk.Columns.Add(c);
                    c.IsUniqueKey = true;
                }
                if (uk.Columns.Count != 0)
                {
                    view.UniqueKeys.Add(uk);
                }
            }
            foreach (var child in table.ForeignKeyChildren)
            {
                view.ForeignKeyChildren.Add(child);
            }
        }
        #endregion

        #region
        protected Type GetDbType(Column column)
        {
            var item = DbTypeInfos.FirstOrDefault(p => p.DbType == column.DbType);
            if (item == null) return null;
            if (column.Name.EndsWith("Color", StringComparison.OrdinalIgnoreCase)
                && item.SystemType.Equals(typeof(Int32)))
            {
                return typeof(System.Drawing.Color);
            }
            return item.SystemType;
        }
        protected void LoadTables()
        {
            foreach (DataRow row in TableTable.Rows)
            {
                var table = new Table();

                table.Name = row["TABLENAME"].ToString();
                table.DisplayName = row["COMMENTS"].ToString();

                #region 加载列
                var columnRows = ColumnTable.Select("TABLENAME='" + table.Name + "'");
                foreach (DataRow columnRow in columnRows)
                {
                    var column = new Column();
                    column.Table = table;
                    column.Name = columnRow["COLUMNNAME"].ToString();
                    column.DbType = columnRow["DATATYPE"].ToString();
                    column.SystemType = GetDbType(column);
                    column.DefaultValue = columnRow["DEFAULTVALUE"];
                    column.DisplayName = columnRow["COMMENTS"].ToString();
                    var nullable = columnRow["NULLABLE"].ToString();
                    column.Nullable = nullable.Equals("true", StringComparison.OrdinalIgnoreCase)
                        || nullable.Equals("T", StringComparison.OrdinalIgnoreCase)
                        || nullable.Equals("yes", StringComparison.OrdinalIgnoreCase)
                        || nullable.Equals("Y", StringComparison.OrdinalIgnoreCase)
                        || nullable.Equals("1");
                    if (columnRow["ORDINAL"] != DBNull.Value)
                    {
                        column.Ordinal = int.Parse(columnRow["ORDINAL"].ToString());
                    }
                    column.IsEnum = IsEnumType(column.SystemType);

                    int length = 0;
                    int.TryParse(columnRow["LENGTH"].ToString(), out length);
                    column.Length = length;

                    int precision = 0;
                    int.TryParse(columnRow["PRECISION"].ToString(), out precision);
                    column.Precision = precision;

                    int scale = 0;
                    int.TryParse(columnRow["SCALE"].ToString(), out scale);
                    column.Scale = scale;
                    table.Columns.Add(column);
                }
                #endregion

                Tables.Add(table);
            }

            foreach (var table in Tables)
            {
                #region 加载主键
                var primaryKeyRows = ConstraintsTable.Select("TABLENAME='" + table.Name + "' AND CONSTRAINTTYPE='P'");
                foreach (DataRow primaryKeyRow in primaryKeyRows)
                {
                    var columnName = primaryKeyRow["COLUMNNAME"].ToString();
                    var column = table.Columns.FirstOrDefault(p => p.Name == columnName);
                    if(column==null)
                    {
                        throw new Exception("表[" + table.Name + "]的主键:" + columnName + "未找到详细定义");
                    }
                    column.IsPrimaryKey = true;
                }
                #endregion

                #region 加载外键
                var foreignKeyRows = ConstraintsTable.Select("TABLENAME='" + table.Name + "' AND CONSTRAINTTYPE='R'");
                foreach (DataRow foreignKeyRow in foreignKeyRows)
                {
                    var foreignKey = new ForeignKey();
                    foreignKey.Table = table;
                    table.ForeignKeys.Add(foreignKey);

                    foreignKey.Name = foreignKeyRow["CONSTRAINTNAME"].ToString();
                    foreignKey.Column = table.Columns.First(
                        p => p.Name == foreignKeyRow["COLUMNNAME"].ToString());
                    foreignKey.Column.IsForeignKey = true;

                    var referenceTable = Tables.FirstOrDefault(
                         p => p.Name == foreignKeyRow["REFERENCETABLE"].ToString());
                    if (referenceTable != null)
                    {
                        foreignKey.ReferenceTable = referenceTable;
                        referenceTable.ForeignKeyChildren.Add(table);
                    }
                }
                #endregion

                #region 加载惟一索引
                var uniqueKeyRows = ConstraintsTable.Select("TABLENAME='" + table.Name + "' AND CONSTRAINTTYPE='U'");
                foreach (DataRow uniqueKeyRow in uniqueKeyRows)
                {
                    var uniqueKeyName = uniqueKeyRow["CONSTRAINTNAME"].ToString();
                    var uniqueKey = table.UniqueKeys.FirstOrDefault(p => p.Name == uniqueKeyName);
                    if (uniqueKey == null)
                    {
                        uniqueKey = new UniqueKey();
                        uniqueKey.Table = table;
                        table.UniqueKeys.Add(uniqueKey);
                        uniqueKey.Name = uniqueKeyRow["CONSTRAINTNAME"].ToString();
                    }
                    var column = table.Columns.First(
                        p => p.Name.Equals(uniqueKeyRow["COLUMNNAME"].ToString(), StringComparison.OrdinalIgnoreCase));
                    uniqueKey.Columns.Add(column);
                    column.IsUniqueKey = true;
                }
                #endregion
            }
        }
        protected void LoadViews()
        {
            foreach (DataRow row in ViewTable.Rows)
            {
                var view = new View();

                view.Name = row["VIEWNAME"].ToString();
                view.Sql = row["SQL"].ToString();

                #region 加载列
                var columnRows = ColumnTable.Select("TABLENAME='" + view.Name + "'");
                foreach (DataRow columnRow in columnRows)
                {
                    var column = new Column();
                    column.Table = view;
                    column.Name = columnRow["COLUMNNAME"].ToString();
                    column.DbType = columnRow["DATATYPE"].ToString();
                    column.SystemType = GetDbType(column);
                    column.DefaultValue = columnRow["DEFAULTVALUE"];
                    column.DisplayName = columnRow["COMMENTS"].ToString();
                    var nullable = columnRow["NULLABLE"].ToString();
                    column.Nullable = nullable.Equals("true", StringComparison.OrdinalIgnoreCase)
                        || nullable.Equals("T", StringComparison.OrdinalIgnoreCase)
                        || nullable.Equals("yes", StringComparison.OrdinalIgnoreCase)
                        || nullable.Equals("Y", StringComparison.OrdinalIgnoreCase) 
                        || nullable.Equals("1");
                    
                    if (columnRow["ORDINAL"] != DBNull.Value)
                    {
                        column.Ordinal = int.Parse(columnRow["ORDINAL"].ToString());
                    }
                    int length = 0;
                    int.TryParse(columnRow["LENGTH"].ToString(), out length);
                    column.Length = length;

                    int precision = 0;
                    int.TryParse(columnRow["PRECISION"].ToString(), out precision);
                    column.Precision = precision;

                    int scale = 0;
                    int.TryParse(columnRow["SCALE"].ToString(), out scale);
                    column.Scale = scale;
                    view.Columns.Add(column);
                }
                #endregion

                #region 根据列找到表,进一步初始化视图列信息
                if (!string.IsNullOrEmpty(SqlViewColumns))
                {
                    IEnumerable<string> tableNames = null;
                    var rows = ViewColumnTable.Select("VIEWNAME='" + view.Name + "'");
                    if (rows.Count() != 0)
                    {
                        tableNames = rows.Select(p => p["TABLENAME"].ToString());
                    }
                    else
                    {
                        throw new Exception(string.Format(Resource.Properties.Resources.TableConstitutesViewNotFound, view.Name));
                    }
                    foreach (var name in tableNames)
                    {
                        var table = Tables.FirstOrDefault(p => p.Name.Equals(name, StringComparison.OrdinalIgnoreCase));
                        if (table == null || view.Tables.Any(p => p == table))
                        {
                            continue;
                        }

                        InitViewByTable(table, view);

                        if (view.Columns.Any(p => p.Table == table))
                        {
                            view.Tables.Add(table);
                        }
                    }
                }
                #endregion
                Views.Add(view);
            }
        }
        protected DataTable GetSchema(DbConnection connection, string schema)
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                return connection.GetSchema(schema);
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }
        public Table GetTable(string tableName)
        {
            return Tables.FirstOrDefault(p => p.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase));
        }
        public View GetView(string viewName)
        {
            return Views.FirstOrDefault(p => p.Name.Equals(viewName, StringComparison.OrdinalIgnoreCase));
        }
        protected  DataTable GetTableDatas(Table table)
        {
            using (var connection = Inaction.DbProvider.Instance.Connection)
            {
                var command = connection.CreateCommand();
                command.CommandText = "Select * from " + table.FormatName;

                var reader = command.ExecuteReader();
                DataTable dataTable = new DataTable();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    dataTable.Columns.Add(reader.GetName(i));
                }
                while (reader.Read())
                {
                    var row = dataTable.NewRow();
                    for (int k = 0; k < reader.FieldCount; k++)
                    {
                        row[k] = reader.GetValue(k);
                    }
                    dataTable.Rows.Add(row);
                }
                reader.Close();
                reader.Dispose();
                return dataTable;
            }
        }
        public virtual string CreateScript(View view)
        {
            return view.Sql;
        }
        #endregion
        #endregion
    }
}
